clear all

*Set working folders. Please change the global variable "user" to the folder with the replication package
global user "C:\Users\weilingliu1990\Dropbox\Insurance Project\LTCI Replication Package"
global data "$user\Data"
global images "$user\Images"
global drafttab "$user\Tables"

set maxvar  32767
*ssc install estout						//will need to install if you don't already have it

**********************************
** COMPANY LEVEL AVG PRICE DATA **
**********************************
use "$data\realized_premium_data.dta", clear

//TABLE 1B
estpost tabstat chg avg_prem inforce_ct earned_prem, columns(statistics) statistics(mean sd min p25 p50 p75 max n) 
esttab using "$drafttab\Table_1B.tex", title(Summary Statistics for Premiums) cells("mean(fmt(%13.2fc)) sd(fmt(%13.2fc)) min(fmt(%13.2fc)) p50(fmt(%13.2fc)) p25(fmt(%13.2fc)) p75(fmt(%13.2fc)) max(fmt(%13.2fc)) count(fmt(%11.0fc))") noobs nomtitle nonumber label replace
eststo clear

//TABLE 9
eststo: reg chg yrs_lag i.state_num i.year ,  vce(cluster co_code)  noconstant
quietly summ chg
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "No"
estadd local cosfe "No"
eststo: reg chg yrs_lag i.state_num i.year i.co_code ,  vce(cluster co_code)
quietly summ chg
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
estadd local cosfe "No"
eststo: reghdfe chg yrs_lag i.state_num i.year ,  absorb(double_cluster2) vce(cluster double_cluster)
quietly summ chg
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "No"
estadd local cosfe "Yes"
eststo: reghdfe chg yrs_lag i.state_num i.year ,  absorb(double_cluster) vce(cluster double_cluster)
quietly summ chg
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "No"
estadd local cosfe "Yes"
eststo: reghdfe chg yrs_lag i.state_num i.year ,  absorb(double_cluster2 double_cluster) vce(cluster double_cluster)
quietly summ chg
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "No"
estadd local cosfe "Yes"
esttab using "$drafttab\Table_9.tex" , b(%4.2f) se(%4.2f) star(* 0.10 ** 0.05 *** 0.01) ///
scalars("ysum Mean Dependent Variable" "fe State FE and Year FE" "cofe Company FE" "cosfe Company x State FE" "N Number of Observations" "r2 R-squared") ///
sfmt(%4.2f %4.2f %4.2f %7.0fc %4.2f %4.3f) noobs lines ///
label keep(yrs_lag ) nogaps numbers nobaselevels f ///
mgroups("Premium Increase", pattern(1 0) span prefix(\multicolumn{@span}{c}{) suffix(}) erepeat(\cline{@span}) lhs( \\) ) ///
nomtitles replace
eststo clear


//FIGURE 4B
binscatter chg yrs_lag, absorb(state_num) controls(i.year) ytitle("Average Annual Premium Increase (%)") xtitle("Years Left in Term") xsc(reverse) ///
lcolor(gray) ylab(, nogrid) graphregion(color(white)) legend(off) yscale(range(2 6)) xscale(range(0 4)) ytick(2(1.0)6) ylabel(#10)
graph export "$images\Figure_4B.png", replace



***************************************
** COMPANY X STATE X YEAR LEVEL DATA **
***************************************
*ssc install spmap			//need to install if you don't have it already

//FIGURE 1
**** CREATE A GEOGRAPHIC MAP ****
use "$data\insurer_state_data", clear
//66915, New York Life
collapse (mean) req_rate appr_rate, by(co_code state)
keep if co_code==66915
sort state
drop if inlist(state,"AS","NA","MP","US","VI","GU")
sort state
save "$data\NY_life_exp", replace

use "$data\usdb", clear
rename STUSPS state
sort state
merge 1:m state using "$data\NY_life_exp"
drop if _merge!=3
drop _merge
format req_rate %4.1f
format appr_rate %4.1f
spmap req_rate using "$data\uscoord" if id !=28 & id!=43 &id!=37,legend(size(medium)) id(id) fcolor(Blues) 
graph export "$images\Figure_1A.png", replace
spmap appr_rate using "$data\uscoord" if id !=28 & id!=43 &id!=37,legend(size(medium)) id(id) fcolor(Greens) 
graph export "$images\Figure_1B.png", replace


** RUN REGRESSIONS SHOWN IN APPENDIX TABLES A1-A7
use "$data\insurer_state_data", clear
egen double_cluster=group(co_code state_num)
egen double_cluster2=group(co_code year)

//TABLE A1
eststo: reghdfe appr_rate c.yrs_left i.state_num  i.year, absorb(double_cluster2 double_cluster) vce(cluster double_cluster) 
quietly summ appr_rate if e(sample)
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
eststo: reghdfe pct_approved c.yrs_left i.state_num i.year,  absorb(double_cluster2 double_cluster) vce(cluster double_cluster) 
quietly summ pct_approved if e(sample)
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
eststo: reghdfe pct_approved2 c.yrs_left i.state_num  i.year,  absorb(double_cluster2 double_cluster) vce(cluster double_cluster) 
quietly summ pct_approved2 if e(sample)
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
esttab using "$drafttab\Table_A1.tex" , b(%4.2f) se(%4.2f) star(* 0.10 ** 0.05 *** 0.01) ///
scalars("ysum Mean Dependent Variable" "fe State FE and Year FE" "cofe Company FE" "N Number of Observations" "r2 R-squared") ///
sfmt(%4.2f %4.2f %4.2f %7.0fc %4.2f %4.3f) noobs lines ///
label keep(yrs_left_in_term) nogaps numbers nobaselevels f ///
mtitle("Size of Increase" "Size Scaled by Request" "Approval Rate, All" "Approval Rate, New") replace
eststo clear
 
//TABLE A2
eststo: reghdfe req_rate c.yrs_left i.state_num i.year, vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
quietly summ req_rate
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
eststo: reghdfe req_policies c.yrs_left i.state_num i.year, vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
quietly summ req_policies
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
esttab using "$drafttab\Table_A2.tex" , b(%4.2f) se(%4.2f) star(* 0.10 ** 0.05 *** 0.01) ///
scalars("ysum Mean Dependent Variable" "fe State FE and Year FE" "cofe Company FE" "N Number of Observations" "r2 R-squared") ///
sfmt(%4.2f %4.2f %4.2f %7.0fc %4.2f %4.3f) noobs lines ///
keep(yrs_left_in_term) varlabel(yrs_left_in_term "Years Left in Term") mtitle( "Size of Requested Increase" "Number of Requests") nogaps numbers nobaselevels f replace
eststo clear


//TABLE A4
encode name, gen(name_num)
*winpct make regulator more immune to political pressures, and approve higher rates
eststo: reghdfe pct_approved c.yrs_left c.winpct i.state_num i.year, vce(cluster co_code) absorb(double_cluster2 double_cluster)
quietly summ pct_approved if winpct<.
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
eststo: reghdfe pct_approved c.yrs_left##c.winpct i.state_num i.year, vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
quietly summ pct_approved if winpct<.
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
eststo: reghdfe pct_approved c.yrs_left##c.winpct i.name_num  i.year, vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
quietly summ pct_approved if winpct<.
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
eststo: reghdfe appr_rate c.yrs_left c.winpct i.state_num i.year , vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
quietly summ appr_rate if winpct<.
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
eststo: reghdfe appr_rate c.yrs_left##c.winpct i.state_num i.year, vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
quietly summ appr_rate if winpct<.
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
eststo: reghdfe appr_rate c.yrs_left##c.winpct i.name_num i.year, vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
quietly summ appr_rate if winpct<.
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
esttab using "$drafttab\Table_A4.tex" , b(%4.2f) se(%4.2f) star(* 0.10 ** 0.05 *** 0.01) ///
scalars("ysum Mean Dependent Variable" "fe State FE and Year FE" "cofe Company FE" "N Number of Observations" "r2 R-squared") ///
sfmt(%4.2f %4.2f %4.2f %7.0fc %4.2f %4.3f) noobs lines ///
label keep(yrs_left_in_term winpct c.yrs_left_in_term#c.winpct) nogaps numbers nobaselevels f ///
mgroups("Approval Probability" "Size of Approved Increase", pattern(1 0 0 1 0 0) span prefix(\multicolumn{@span}{c}{) suffix(}) erepeat(\cline{@span}) lhs( \\) ) ///
nomtitles replace
 eststo clear
 

//TABLE A5
* encode name, gen(name_num)
eststo: reghdfe pct_approved c.yrs_left c.margin i.state_num i.co_code i.year, vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
quietly summ pct_approved if margin<.
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
estadd local regfe "No"
eststo: reghdfe pct_approved c.yrs_left##c.margin i.state_num, vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
quietly summ pct_approved if margin<.
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
estadd local regfe "No"
eststo: reghdfe pct_approved c.yrs_left##c.margin i.name_num i.state_num, vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
quietly summ pct_approved if margin<.
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
estadd local regfe "Yes"
eststo: reghdfe appr_rate c.yrs_left c.margin i.state_num , vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
quietly summ appr_rate if margin<.
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
estadd local regfe "No"
eststo: reghdfe appr_rate c.yrs_left##c.margin i.state_num , vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
quietly summ appr_rate if margin<.
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
estadd local regfe "No"
eststo: reghdfe appr_rate c.yrs_left##c.margin i.name_num  i.year  , vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
quietly summ appr_rate if margin<.
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
estadd local regfe "Yes"
esttab using "$drafttab\Table_A5.tex" , b(%4.2f) se(%4.2f) star(* 0.10 ** 0.05 *** 0.01) ///
scalars("ysum Mean Dependent Variable" "fe State FE and Year FE" "cofe Company FE" "regfe Regulator FE" "N Number of Observations" "r2 R-squared") ///
sfmt(%4.2f %4.2f %4.2f %7.0fc %4.2f %4.3f) noobs lines ///
label keep(yrs_left_in_term margin c.yrs_left_in_term#c.margin ) nogaps numbers nobaselevels f ///
mgroups("Prob of Approval" "Size of Increase", pattern(1 0 0 1 0 0) span prefix(\multicolumn{@span}{c}{) suffix(}) erepeat(\cline{@span}) lhs( \\) ) ///
nomtitles replace
 eststo clear

sort state year
merge m:1 state year using "$data\commissioner_data"
drop if _merge==2	//not merged are just the extra years
drop _merge

gen chg_rep= chg_in_party==1
gen chg_dem= chg_in_party==-1
gen close_race_dem= chg_in_party==-1 & margin<10
gen close_race_rep= chg_in_party==1 & margin<10
gen close_race=margin<10

gen democrat= (political_party=="D" | political_party==".D")
label var democrat "Democrat"

//TABLE A3
 //new jun 22: removed close_race_dem due to small number of obs
eststo: reghdfe pct_approved c.yrs_left c.democrat i.state_num i.co_code i.year, vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
quietly summ pct_approved 
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
eststo: reghdfe pct_approved c.yrs_left##c.democrat i.state_num i.co_code i.year, vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
quietly summ pct_approved
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
eststo: reghdfe pct_approved c.yrs_left chg_rep chg_dem i.state_num i.year i.co_code, vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
quietly summ appr_rate
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
eststo: reghdfe pct_approved close_race c.yrs_left close_race_rep i.state_num i.year i.co_code , vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
quietly summ appr_rate 
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"

eststo: reghdfe appr_rate c.yrs_left c.democrat i.state_num i.year i.co_code, vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
quietly summ appr_rate
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
eststo: reghdfe appr_rate c.yrs_left##c.democrat i.state_num i.year i.co_code , vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
quietly summ appr_rate 
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
eststo: reghdfe appr_rate c.yrs_left chg_rep chg_dem i.state_num i.year i.co_code , vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
quietly summ appr_rate 
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
eststo: reghdfe appr_rate c.yrs_left close_race close_race_rep i.state_num i.year i.co_code , vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
quietly summ appr_rate 
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
esttab using "$drafttab\Table_A3.tex" , b(%4.2f) se(%4.2f) star(* 0.10 ** 0.05 *** 0.01) ///
scalars("ysum Mean Dependent Variable" "fe State FE and Year FE" "cofe Company FE" "N Number of Observations" "r2 R-squared") ///
sfmt(%4.2f %4.2f %4.2f %7.0fc %4.2f %4.3f) noobs lines interaction(x) label ///
keep(*close_race* c.yrs_left_in_term#c.democrat yrs_left_in_term democrat chg_dem chg_rep) varlabel(c.yrs_left_in_term#c.democrat "Years Left in Term x Democrat") nogaps numbers nobaselevels f ///
mgroups("Approval Probability" "Size of Approved Increase", pattern(1 0 1 0) span prefix(\multicolumn{@span}{c}{) suffix(}) erepeat(\cline{@span}) lhs( \\) ) ///
nomtitles replace
eststo clear


sort year state
merge m:1 year state using "$data\commissioner_placebo_data"


//TABLE A6
eststo: reghdfe pct_approved c.yrs_left_in_term i.year i.state_num i.co_code if elected==1, vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
quietly summ pct_approved if elected==1
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
eststo: reghdfe appr_rate c.yrs_left_in_term i.year i.state_num i.co_code if elected==1, vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
quietly summ appr_rate if elected==1
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
eststo: reghdfe pct_approved c.yrs_left_in_term i.year i.state_num i.co_code if elected==0, vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
quietly summ pct_approved if elected==0
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
eststo: reghdfe appr_rate c.yrs_left_in_term i.year i.state_num i.co_code if elected==0, vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
quietly summ appr_rate if elected==0
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
esttab using "$drafttab\Table_A6.tex" , b(%4.2f) se(%4.2f) star(* 0.10 ** 0.05 *** 0.01) ///
scalars("ysum Mean Dependent Variable" "fe State FE and Year FE" "cofe Company FE" "N Number of Observations" "r2 R-squared") ///
sfmt(%4.2f %4.2f %4.2f %7.0fc %4.2f %4.3f) noobs lines label ///
 keep(*yrs_left* )  mtitle( "Approval Probability" "Size of Approved Increase" "Approval Probability" "Size of Approved Increase") nogaps numbers nobaselevels f ///
mgroups("Commissioner Directly Elected" "Appointed Commissioner", pattern(1 0 1 0) span prefix(\multicolumn{@span}{c}{) suffix(}) erepeat(\cline{@span}) lhs( \\) ) ///
replace
eststo clear


//TABLE A7
drop _merge
merge m:1 state year using "$data\campaign_finance_data.dta"
drop if _merge==2 //non-elected states are not matched
drop _merge

replace contributions = contributions/100000
replace cash = cash/100000

eststo: reghdfe pct_approved c.yrs_left_in_term c.cash i.co_code i.year i.state_num, vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
 quietly summ pct_approved if cash<. & cont<.
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
eststo: reghdfe pct_approved c.yrs_left_in_term c.cont  i.co_code i.year i.state_num,  vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
 quietly summ pct_approved if cash<. & cont<.
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
eststo: reghdfe pct_approved c.yrs_left_in_term c.cont c.cash i.co_code i.year i.state_num,  vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
 quietly summ pct_approved if cash<. & cont<.
estadd local fe "Yes"
estadd local cofe "Yes"

eststo: reghdfe appr_rate c.yrs_left_in_term c.cash   i.co_code i.year i.state_num, vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
 quietly summ appr_rate if cash<. & cont<.
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
eststo: reghdfe appr_rate c.yrs_left_in_term c.cont i.co_code i.year i.state_num,   vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
 quietly summ appr_rate if cash<. & cont<.
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"
eststo: reghdfe appr_rate c.yrs_left_in_term c.cont c.cash i.co_code i.year i.state_num, vce(cluster double_cluster) absorb(double_cluster2 double_cluster)
 quietly summ appr_rate if cash<. & cont<.
estadd scalar ysum = r(mean)
estadd local fe "Yes"
estadd local cofe "Yes"

esttab using "$drafttab\Table_A7.tex" , b(%4.2f) se(%4.2f) star(* 0.10 ** 0.05 *** 0.01) ///
scalars("ysum Mean Dependent Variable" "fe State FE and Year FE" "cofe Company FE" "N Number of Observations" "r2 R-squared") ///
sfmt(%4.2f %4.2f %4.2f %7.0fc %4.2f %4.3f) noobs lines label ///
 keep(yrs_left_in_term cashonhand contributions) ///
 varlabel(_cons "Constant" ///
 cashonhand "Prev Contributions Raised before Year Start" contributions "New Contributions Raised over the Year") nogaps numbers nobaselevels f ///
mgroups("Prob of Approval" "Size of Increase", pattern(1 0 0 1 0 0) span prefix(\multicolumn{@span}{c}{) suffix(}) erepeat(\cline{@span}) lhs( \\) ) ///
nomtitles replace
eststo clear
